package org.shj.plugin.tdpl.gen.db;

import java.util.List;
import java.util.Map;

import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;
import org.apache.log4j.Logger;
import org.shj.tdpl.common.constant.TdplConstants;
import org.shj.tdpl.common.utils.StringUtil;

/**
 * @author Shen Huang Jian
 */
@Getter
@Setter
@EqualsAndHashCode(callSuper = true)
public class MysqlDbConnect extends AbstractDbConnect {
	
	private Logger log = Logger.getLogger(getClass());
	
	@Override
	protected List<Map<String, Object>> retrieveTableColumnsList() {
		StringBuffer sql = new StringBuffer();
		sql.append("select col.TABLE_NAME, col.COLUMN_NAME, col.DATA_TYPE, col.CHARACTER_MAXIMUM_LENGTH AS DATA_LENGTH, col.IS_NULLABLE, ")
		   .append("COLUMN_KEY from information_schema.COLUMNS col ")
		   .append("INNER JOIN information_schema.TABLES tbl on col.TABLE_NAME = tbl.TABLE_NAME and col.TABLE_SCHEMA=tbl.TABLE_SCHEMA ")
		   .append("WHERE col.TABLE_SCHEMA='").append(schema).append("' ");
		
		String exculdeInclude =  excludeIncludeTablesSql("tbl.TABLE_NAME");
		if(exculdeInclude != null){
			sql.append(" AND ").append(exculdeInclude);
		}
				
		sql.append("ORDER BY tbl.TABLE_NAME, col.COLUMN_NAME ");
		log.debug("11111:  " + sql);
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString());
		
		if(list != null && !list.isEmpty()){
			String isNullable = null;
			String isPk = null;
			for(Map<String, Object> map : list){
				isNullable = (String)map.get("IS_NULLABLE");
				map.put(TdplConstants.NULLABLE, "YES".equalsIgnoreCase(isNullable) ? "Y" : "N");
				map.remove("IS_NULLABLE");
				
				isPk = (String)map.get("COLUMN_KEY");
				map.put(TdplConstants.IS_PK, "PRI".equalsIgnoreCase(isPk) ? "Y" : "N");
				map.remove("COLUMN_KEY");
			}
		}
		return list;
	}

	@Override
	protected List<Map<String, Object>> getAllTableNames() {
		StringBuffer sql = new StringBuffer();
		sql.append("select TABLE_NAME from information_schema.TABLES ")
		   .append(" WHERE TABLE_SCHEMA='").append(schema).append("'");
		
		String exculdeInclude =  excludeIncludeTablesSql("TABLE_NAME");
		if(exculdeInclude != null){
			sql.append(" and ").append(exculdeInclude);
		}
		
		log.debug(sql);
		return jdbcTemplate.queryForList(sql.toString());
	}

	@Override
	protected List<Map<String, Object>> retrieveChildAndParentList(String fkIsNullable) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT DISTINCT A.TABLE_NAME , A.referenced_table_name PARENT_TABLE, A.COLUMN_NAME FK_COL_NM, B.IS_NULLABLE ")
		   .append("FROM information_schema.KEY_COLUMN_USAGE A, information_schema.COLUMNS B, information_schema.TABLES C ")
		   .append("WHERE C.TABLE_NAME = B.TABLE_NAME ")
		   .append("AND C.TABLE_SCHEMA='").append(schema).append("' ")
		   .append("AND A.referenced_table_name is not null ")
		   .append("AND A.TABLE_NAME = B.TABLE_NAME ")
		   .append("AND A.COLUMN_NAME = B.COLUMN_NAME ")
		   .append("AND B.IS_NULLABLE = '").append("Y".equalsIgnoreCase(fkIsNullable) ? "YES" : "NO").append("' ");
		//ignore the foreign key can be null. It will effect the insert and delete order when it isn't null
		
		
		String exculdeInclude =  excludeIncludeTablesSql("A.TABLE_NAME");
		if(exculdeInclude != null){
			sql.append(" AND ").append(exculdeInclude);
		}
		log.debug(sql);
		return jdbcTemplate.queryForList(sql.toString());
	}

	@Override
	public String genEqValue(String columnType, String cellNm){
		StringBuffer value = new StringBuffer();
		columnType = columnType.toUpperCase();
		if(columnType.indexOf("INT") != -1 || "FLOAT".equals(columnType) || "DOUBLE".equals(columnType) || "DECIMAL".equals(columnType)){							
			value.append("\"&").append(cellNm).append("&\"");
        }else if("DATE".equals(columnType)){
			value.append("\"&IF(ISNUMBER(SEARCH(\"now()\", ").append(cellNm).append(")), ")
					.append(cellNm).append(", \"STR_TO_DATE('\"&").append(cellNm)
					.append("&\"', '%Y-%m-%d')\")&\"");
        }else if("TIME".equals(columnType)){
            value.append("STR_TO_DATE('\"&").append(cellNm).append("&\"','%T')");

        }else if("DATETIME".equals(columnType) || "TIMESTAMP".equals(columnType)){
            value.append("\"&IF(ISNUMBER(SEARCH(\"now()\", ").append(cellNm).append(")), ")
                    .append(cellNm).append(", \"STR_TO_DATE('\"&").append(cellNm)
                    .append("&\"', '%Y-%m-%d %T')\")&\"");

        }else{
			value.append("'\"&").append(cellNm).append("&\"'");
		}

		return value.toString();
	}

	@Override
	protected boolean isAuditTableExist(String auditTblNm){
		if(StringUtil.isEmpty(auditTblNm)){
			return false;
		}
		String sql = "select TABLE_NAME FROM information_schema.TABLES  where TABLE_NAME = '" + auditTblNm + "'";
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
		return list != null && !list.isEmpty();
	}

}
